﻿Imports System
Imports System.Data
Imports System.Configuration
Imports System.Collections
Imports System.Web
Imports System.Web.Security
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports System.Web.UI.WebControls.WebParts
Imports System.Web.UI.HtmlControls
Imports Microsoft.Reporting.WebForms
Imports log4net

Partial Class Report_RP16
    Inherits System.Web.UI.Page

    Public ScriptText As String
    Dim Report As Object
    Private Shared logger As ILog = LogManager.GetLogger("Report_RP16")

    Protected Sub Page_Init(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Init

        'If Session("fundcenterS") = "" Then
        '    Session("fundcenterS") = "0011"
        '    Session("fundcenterE") = "0012"

        'End If

        'If Session("users") = "" Then
        '    Session("users") = "wassana"
        'End If
        'Session("year") = 2556
        'Session("version") = 2


        Getdata()


    End Sub


    Function Getdata() As Decimal


        Dim adapter As New Data_Report3TableAdapters.RP16TableAdapter
        Dim sql As String
        Dim dt As New Data_Report3.RP16DataTable


        sql = " select  x.planyear,x.funcenter_code,x.funcenter_name,x.BudgetType,x.CmmtParent1,x.CmmtParent2,x.cmmt_Code,x.cmmt_name,sum(x.amount) as amount,"


        sql += " (select sum(y.amount) from (SELECT BudgetDetails.[Amount] as amount,BudgetDetails.[PlanYear] as planyear,BudgetDetails.[Version] as version,CASE ISNULL(BudgetDetails.[Project_ProjectCode],'') WHEN '' THEN 'งบประมาณประจำ' ELSE "
        sql += " CASE LEFT(ISNULL(BudgetDetails.[Project_ProjectCode],''),1) WHEN 'P' THEN 'งบประมาณโครงการ'"
        sql += " ELSE 'งบประมาณสนับสนุน' END"
        sql += " END AS BudgetType"
        sql += " ,BudgetDetails.[FundCenter_FundCenterCode] as funcenter_code"
        sql += " ,Fundcenters.Description AS funcenter_name"
        sql += " ,BudgetDetails.[Cmmt_CmmtCode] as cmmt_Code"
        sql += " ,(SELECT CmmtHierarchies.Description FROM CmmtHierarchies WHERE CmmtHierarchies.Id = ( SELECT  [ParentCmmtHierarchy_Id] FROM CmmtHierarchies WHERE CmmtGroup = '1' AND CmmtHierarchies.Id = "
        sql += " ( SELECT  [ParentCmmtHierarchy_Id]  FROM CmmtHierarchies WHERE CmmtGroup = '1' AND [Cmmt_CmmtCode] = BudgetDetails.[Cmmt_CmmtCode]  AND [PlanYear] = BudgetDetails.[Cmmt_PlanYear] )"
        sql += " AND [PlanYear] = BudgetDetails.[Cmmt_PlanYear]) AND [PlanYear] = BudgetDetails.[Cmmt_PlanYear]) AS CmmtParent1,(SELECT CmmtHierarchies.Description"
        sql += " FROM CmmtHierarchies WHERE CmmtGroup = '1' AND CmmtHierarchies.Id = ( SELECT  [ParentCmmtHierarchy_Id] FROM CmmtHierarchies WHERE CmmtGroup = '1' AND [Cmmt_CmmtCode] = BudgetDetails.[Cmmt_CmmtCode]"
        sql += " AND [PlanYear] = BudgetDetails.[Cmmt_PlanYear] ) AND [PlanYear] = BudgetDetails.[Cmmt_PlanYear]) AS CmmtParent2"
        sql += " ,Cmmts.Description as cmmt_name"
        sql += " FROM BudgetDetails"
        sql += " INNER JOIN Cmmts ON BudgetDetails.[Cmmt_CmmtCode]= Cmmts.[CmmtCode] and BudgetDetails.[PlanYear]= Cmmts.[PlanYear]"
        sql += " INNER JOIN CmmtHierarchies ON BudgetDetails.[Cmmt_CmmtCode] = CmmtHierarchies.[Cmmt_CmmtCode] and BudgetDetails.[PlanYear]= CmmtHierarchies.[PlanYear] AND CmmtHierarchies.CmmtGroup = '1'"
        sql += " INNER JOIN FundCenters ON BudgetDetails.[FundCenter_FundCenterCode] = Fundcenters.FundCenterCode"
        sql += "  AND BudgetDetails.[FundCenter_PlanYear] = FundCenters.PlanYear "

        sql += " ) as y where y.planyear = x.planyear + 1 and y.version = x.version  and y.funcenter_code = x.funcenter_code and y.BudgetType = x.BudgetType and y.CmmtParent1 = x.CmmtParent1 and y.CmmtParent2 = x.CmmtParent2 and y.cmmt_Code = x.cmmt_Code"
        sql += "  group by planyear,funcenter_code,funcenter_name,BudgetType,CmmtParent1,CmmtParent2,cmmt_Code,cmmt_name )"

        sql += "  as amount1"

        sql += " ,"


        sql += " (select sum(y.amount) from (SELECT BudgetDetails.[Amount] as amount,BudgetDetails.[PlanYear] as planyear,BudgetDetails.[Version] as version,CASE ISNULL(BudgetDetails.[Project_ProjectCode],'') WHEN '' THEN 'งบประมาณประจำ' ELSE "
        sql += " CASE LEFT(ISNULL(BudgetDetails.[Project_ProjectCode],''),1) WHEN 'P' THEN 'งบประมาณโครงการ'"
        sql += " ELSE 'งบประมาณสนับสนุน' END"
        sql += " END AS BudgetType"
        sql += " ,BudgetDetails.[FundCenter_FundCenterCode] as funcenter_code"
        sql += " ,Fundcenters.Description AS funcenter_name"
        sql += " ,BudgetDetails.[Cmmt_CmmtCode] as cmmt_Code"
        sql += " ,(SELECT CmmtHierarchies.Description FROM CmmtHierarchies WHERE CmmtHierarchies.Id = ( SELECT  [ParentCmmtHierarchy_Id] FROM CmmtHierarchies WHERE CmmtGroup = '1' AND CmmtHierarchies.Id = "
        sql += " ( SELECT  [ParentCmmtHierarchy_Id]  FROM CmmtHierarchies WHERE CmmtGroup = '1' AND [Cmmt_CmmtCode] = BudgetDetails.[Cmmt_CmmtCode]  AND [PlanYear] = BudgetDetails.[Cmmt_PlanYear] )"
        sql += " AND [PlanYear] = BudgetDetails.[Cmmt_PlanYear]) AND [PlanYear] = BudgetDetails.[Cmmt_PlanYear]) AS CmmtParent1,(SELECT CmmtHierarchies.Description"
        sql += " FROM CmmtHierarchies WHERE CmmtGroup = '1' AND CmmtHierarchies.Id = ( SELECT  [ParentCmmtHierarchy_Id] FROM CmmtHierarchies WHERE CmmtGroup = '1' AND [Cmmt_CmmtCode] = BudgetDetails.[Cmmt_CmmtCode]"
        sql += " AND [PlanYear] = BudgetDetails.[Cmmt_PlanYear] ) AND [PlanYear] = BudgetDetails.[Cmmt_PlanYear]) AS CmmtParent2"
        sql += " ,Cmmts.Description as cmmt_name"
        sql += "  FROM BudgetDetails"
        sql += " INNER JOIN Cmmts ON BudgetDetails.[Cmmt_CmmtCode]= Cmmts.[CmmtCode] and BudgetDetails.[PlanYear]= Cmmts.[PlanYear]"
        sql += " INNER JOIN CmmtHierarchies ON BudgetDetails.[Cmmt_CmmtCode] = CmmtHierarchies.[Cmmt_CmmtCode] and BudgetDetails.[PlanYear]= CmmtHierarchies.[PlanYear] AND CmmtHierarchies.CmmtGroup = '1'"
        sql += "  INNER JOIN FundCenters ON BudgetDetails.[FundCenter_FundCenterCode] = Fundcenters.FundCenterCode"
        sql += "  AND BudgetDetails.[FundCenter_PlanYear] = FundCenters.PlanYear "

        sql += " ) as y where y.planyear = x.planyear + 2  and y.version = x.version and y.funcenter_code = x.funcenter_code and y.BudgetType = x.BudgetType and y.CmmtParent1 = x.CmmtParent1 and y.CmmtParent2 = x.CmmtParent2 and y.cmmt_Code = x.cmmt_Code"
        sql += " group by planyear,funcenter_code,funcenter_name,BudgetType,CmmtParent1,CmmtParent2,cmmt_Code,cmmt_name )"

        sql += " as amount2"
        sql += " ,"


        sql += " (select sum(y.amount) from (SELECT BudgetDetails.[Amount] as amount,BudgetDetails.[PlanYear] as planyear,BudgetDetails.[Version] as version,CASE ISNULL(BudgetDetails.[Project_ProjectCode],'') WHEN '' THEN 'งบประมาณประจำ' ELSE "
        sql += " CASE LEFT(ISNULL(BudgetDetails.[Project_ProjectCode],''),1) WHEN 'P' THEN 'งบประมาณโครงการ'"
        sql += " ELSE 'งบประมาณสนับสนุน' END"
        sql += " END AS BudgetType"
        sql += " ,BudgetDetails.[FundCenter_FundCenterCode] as funcenter_code"
        sql += " ,Fundcenters.Description AS funcenter_name"
        sql += " ,BudgetDetails.[Cmmt_CmmtCode] as cmmt_Code"
        sql += " ,(SELECT CmmtHierarchies.Description FROM CmmtHierarchies WHERE CmmtHierarchies.Id = ( SELECT  [ParentCmmtHierarchy_Id] FROM CmmtHierarchies WHERE CmmtGroup = '1' AND CmmtHierarchies.Id = "
        sql += " ( SELECT  [ParentCmmtHierarchy_Id]  FROM CmmtHierarchies WHERE CmmtGroup = '1' AND [Cmmt_CmmtCode] = BudgetDetails.[Cmmt_CmmtCode]  AND [PlanYear] = BudgetDetails.[Cmmt_PlanYear] )"
        sql += " AND [PlanYear] = BudgetDetails.[Cmmt_PlanYear]) AND [PlanYear] = BudgetDetails.[Cmmt_PlanYear]) AS CmmtParent1,(SELECT CmmtHierarchies.Description"
        sql += " FROM CmmtHierarchies WHERE CmmtGroup = '1' AND CmmtHierarchies.Id = ( SELECT  [ParentCmmtHierarchy_Id] FROM CmmtHierarchies WHERE CmmtGroup = '1' AND [Cmmt_CmmtCode] = BudgetDetails.[Cmmt_CmmtCode]"
        sql += " AND [PlanYear] = BudgetDetails.[Cmmt_PlanYear] ) AND [PlanYear] = BudgetDetails.[Cmmt_PlanYear]) AS CmmtParent2"
        sql += " ,Cmmts.Description as cmmt_name"
        sql += " FROM BudgetDetails"
        sql += " INNER JOIN Cmmts ON BudgetDetails.[Cmmt_CmmtCode]= Cmmts.[CmmtCode] and BudgetDetails.[PlanYear]= Cmmts.[PlanYear]"
        sql += " INNER JOIN CmmtHierarchies ON BudgetDetails.[Cmmt_CmmtCode] = CmmtHierarchies.[Cmmt_CmmtCode] and BudgetDetails.[PlanYear]= CmmtHierarchies.[PlanYear] AND CmmtHierarchies.CmmtGroup = '1'"
        sql += "  INNER JOIN FundCenters ON BudgetDetails.[FundCenter_FundCenterCode] = Fundcenters.FundCenterCode"
        sql += " AND BudgetDetails.[FundCenter_PlanYear] = FundCenters.PlanYear "

        sql += "  ) as y where y.planyear = x.planyear + 3  and y.version = x.version and y.funcenter_code = x.funcenter_code and y.BudgetType = x.BudgetType and y.CmmtParent1 = x.CmmtParent1 and y.CmmtParent2 = x.CmmtParent2 and y.cmmt_Code = x.cmmt_Code"
        sql += "  group by planyear,funcenter_code,funcenter_name,BudgetType,CmmtParent1,CmmtParent2,cmmt_Code,cmmt_name )"

        sql += " as amount3"

        sql += "  ,"


        sql += " (select sum(y.amount) from (SELECT BudgetDetails.[Amount] as amount,BudgetDetails.[PlanYear] as planyear,BudgetDetails.[Version] as version,CASE ISNULL(BudgetDetails.[Project_ProjectCode],'') WHEN '' THEN 'งบประมาณประจำ' ELSE "
        sql += " CASE LEFT(ISNULL(BudgetDetails.[Project_ProjectCode],''),1) WHEN 'P' THEN 'งบประมาณโครงการ'"
        sql += " ELSE 'งบประมาณสนับสนุน' END"
        sql += " END AS BudgetType"
        sql += " ,BudgetDetails.[FundCenter_FundCenterCode] as funcenter_code"
        sql += " ,Fundcenters.Description AS funcenter_name"
        sql += " ,BudgetDetails.[Cmmt_CmmtCode] as cmmt_Code"
        sql += " ,(SELECT CmmtHierarchies.Description FROM CmmtHierarchies WHERE CmmtHierarchies.Id = ( SELECT  [ParentCmmtHierarchy_Id] FROM CmmtHierarchies WHERE CmmtGroup = '1' AND CmmtHierarchies.Id = "
        sql += " ( SELECT  [ParentCmmtHierarchy_Id]  FROM CmmtHierarchies WHERE CmmtGroup = '1' AND [Cmmt_CmmtCode] = BudgetDetails.[Cmmt_CmmtCode]  AND [PlanYear] = BudgetDetails.[Cmmt_PlanYear] )"
        sql += " AND [PlanYear] = BudgetDetails.[Cmmt_PlanYear]) AND [PlanYear] = BudgetDetails.[Cmmt_PlanYear]) AS CmmtParent1,(SELECT CmmtHierarchies.Description"
        sql += " FROM CmmtHierarchies WHERE CmmtGroup = '1' AND CmmtHierarchies.Id = ( SELECT  [ParentCmmtHierarchy_Id] FROM CmmtHierarchies WHERE CmmtGroup = '1' AND [Cmmt_CmmtCode] = BudgetDetails.[Cmmt_CmmtCode]"
        sql += " AND [PlanYear] = BudgetDetails.[Cmmt_PlanYear] ) AND [PlanYear] = BudgetDetails.[Cmmt_PlanYear]) AS CmmtParent2"
        sql += " ,Cmmts.Description as cmmt_name"
        sql += "  FROM BudgetDetails"
        sql += " INNER JOIN Cmmts ON BudgetDetails.[Cmmt_CmmtCode]= Cmmts.[CmmtCode] and BudgetDetails.[PlanYear]= Cmmts.[PlanYear]"
        sql += " INNER JOIN CmmtHierarchies ON BudgetDetails.[Cmmt_CmmtCode] = CmmtHierarchies.[Cmmt_CmmtCode] and BudgetDetails.[PlanYear]= CmmtHierarchies.[PlanYear] AND CmmtHierarchies.CmmtGroup = '1'"
        sql += " INNER JOIN FundCenters ON BudgetDetails.[FundCenter_FundCenterCode] = Fundcenters.FundCenterCode"
        sql += "  AND BudgetDetails.[FundCenter_PlanYear] = FundCenters.PlanYear "

        sql += " ) as y where y.planyear = x.planyear + 4  and y.version = x.version and y.funcenter_code = x.funcenter_code and y.BudgetType = x.BudgetType and y.CmmtParent1 = x.CmmtParent1 and y.CmmtParent2 = x.CmmtParent2 and y.cmmt_Code = x.cmmt_Code"
        sql += " group by planyear,funcenter_code,funcenter_name,BudgetType,CmmtParent1,CmmtParent2,cmmt_Code,cmmt_name )"

        sql += " as amount4"

        sql += " from ( SELECT BudgetDetails.[Amount] as amount,BudgetDetails.[PlanYear] as planyear,BudgetDetails.[Version] as version,CASE ISNULL(BudgetDetails.[Project_ProjectCode],'') WHEN '' THEN 'งบประมาณประจำ' ELSE "
        sql += "  CASE LEFT(ISNULL(BudgetDetails.[Project_ProjectCode],''),1) WHEN 'P' THEN 'งบประมาณโครงการ'"
        sql += "  ELSE 'งบประมาณสนับสนุน' END"
        sql += "  END AS BudgetType"
        sql += " ,BudgetDetails.[FundCenter_FundCenterCode] as funcenter_code"
        sql += " ,Fundcenters.Description AS funcenter_name"
        sql += " ,BudgetDetails.[Cmmt_CmmtCode] as cmmt_Code"
        sql += " ,(SELECT CmmtHierarchies.Description FROM CmmtHierarchies WHERE CmmtHierarchies.Id = ( SELECT  [ParentCmmtHierarchy_Id] FROM CmmtHierarchies WHERE CmmtGroup = '1' AND CmmtHierarchies.Id = "
        sql += " ( SELECT  [ParentCmmtHierarchy_Id]  FROM CmmtHierarchies WHERE CmmtGroup = '1' AND [Cmmt_CmmtCode] = BudgetDetails.[Cmmt_CmmtCode]  AND [PlanYear] = BudgetDetails.[Cmmt_PlanYear] )"
        sql += " AND [PlanYear] = BudgetDetails.[Cmmt_PlanYear]) AND [PlanYear] = BudgetDetails.[Cmmt_PlanYear]) AS CmmtParent1,(SELECT CmmtHierarchies.Description"
        sql += " FROM CmmtHierarchies WHERE CmmtGroup = '1' AND CmmtHierarchies.Id = ( SELECT  [ParentCmmtHierarchy_Id] FROM CmmtHierarchies WHERE CmmtGroup = '1' AND [Cmmt_CmmtCode] = BudgetDetails.[Cmmt_CmmtCode]"
        sql += " AND [PlanYear] = BudgetDetails.[Cmmt_PlanYear] ) AND [PlanYear] = BudgetDetails.[Cmmt_PlanYear]) AS CmmtParent2"
        sql += " ,Cmmts.Description as cmmt_name"
        sql += "  FROM BudgetDetails"
        sql += " INNER JOIN Cmmts ON BudgetDetails.[Cmmt_CmmtCode]= Cmmts.[CmmtCode] and BudgetDetails.[PlanYear]= Cmmts.[PlanYear]"
        sql += " INNER JOIN CmmtHierarchies ON BudgetDetails.[Cmmt_CmmtCode] = CmmtHierarchies.[Cmmt_CmmtCode] and BudgetDetails.[PlanYear]= CmmtHierarchies.[PlanYear] AND CmmtHierarchies.CmmtGroup = '1'"
        sql += " INNER JOIN FundCenters ON BudgetDetails.[FundCenter_FundCenterCode] = Fundcenters.FundCenterCode"
        sql += " AND BudgetDetails.[FundCenter_PlanYear] = FundCenters.PlanYear  "



        sql += " ) as x"


        sql += " where  x.PlanYear  = " & Session("year") & " and x.version = '" & Session("version") & "'"
        If Session("FCTRmode") = "1" Then
            sql += " and  x.funcenter_code IN (" & Session("FundcenterAll") & ") "
        ElseIf Session("FCTRmode") = "2" Then
            sql += " and  x.funcenter_code IN (" & Session("FundcenterAll") & ") "
        End If


        If Session("CmmtMode") = "1" Then
            If Val(Session("CmmtCodeS")) <> 0 And Val(Session("CmmtCodeE")) <> 0 Then
                sql += " and  x.cmmt_Code  between " & Session("CmmtCodeS") & " and " & Session("CmmtCodeE")
            End If
            If Val(Session("CmmtCodeS")) <> 0 And Val(Session("CmmtCodeE")) = 0 Then
                sql += " and  x.cmmt_Code = " & Session("CmmtCodeS")
            End If
            If Val(Session("CmmtCodeS")) = 0 And Val(Session("CmmtCodeE")) <> 0 Then
                sql += " and  x.cmmt_Code = " & Session("CmmtCodeE")
            End If
            If Val(Session("CmmtCodeS")) = 0 And Val(Session("CmmtCodeE")) = 0 Then
                sql += " and  x.cmmt_Code  between " & "00000000" & " and " & "99999999"
            End If
        ElseIf Session("CmmtMode") = "2" Then
            If Session("CmmtParent") <> "" And Session("CmmtParent") <> "0" Then
                sql += "and x.cmmt_Code in (select cmmt_cmmtcode from  cmmthierarchies where parentcmmthierarchy_id in (select id from cmmthierarchies "
                sql += " where id = " & Session("CmmtParent") & " or parentcmmthierarchy_id = " & Session("CmmtParent") & ")"
                sql += " or id in (select id from cmmthierarchies "
                sql += " where id = " & Session("CmmtParent") & " or parentcmmthierarchy_id = " & Session("CmmtParent") & ")"
                sql += ")"
            End If
        End If




        sql += " group by  x.planyear,x.version,x.funcenter_code,x.funcenter_name,x.BudgetType,x.CmmtParent1,x.CmmtParent2,x.cmmt_Code,x.cmmt_name"


        ' Response.Write(sql)
        ' Exit Function
        adapter.GetDataByFilter(dt, sql)



        'Return 1
        'Exit Function
        dt.TableName = "R16DT"
        Dim das As New DataSet
        das.DataSetName = "R01DS"
        das.Tables.Add(dt)
        ReportViewer1.ProcessingMode = Microsoft.Reporting.WebForms.ProcessingMode.Local
        'ReportViewer1.LocalReport.ReportPath = System.Environment.CurrentDirectory & "Report\R01.rdlc"
        ReportViewer1.LocalReport.DataSources.Clear()
        ReportViewer1.LocalReport.DataSources.Add(New Microsoft.Reporting.WebForms.ReportDataSource("DataSet1", das.Tables("R16DT")))
        ReportViewer1.DocumentMapCollapsed = True

        Dim UserName As String = Session("users")
        Dim years As String = Session("year")
        Dim version As String = Session("version")
        Dim users As String = Session("users")

        Dim p1, p2, p3, y, y1, y2, y3, y4 As ReportParameter
        p1 = New ReportParameter("year", years)
        p2 = New ReportParameter("version", version)
        p3 = New ReportParameter("username", UserName)
        y = New ReportParameter("yearY", years)
        y1 = New ReportParameter("yearY1", years + 1)
        y2 = New ReportParameter("YearY2", years + 2)
        y3 = New ReportParameter("YearY3", years + 3)
        y4 = New ReportParameter("YearY4", years + 4)

        ReportViewer1.LocalReport.SetParameters(p1)
        ReportViewer1.LocalReport.SetParameters(p2)
        ReportViewer1.LocalReport.SetParameters(p3)
        ReportViewer1.LocalReport.SetParameters(y)
        ReportViewer1.LocalReport.SetParameters(y1)
        ReportViewer1.LocalReport.SetParameters(y2)
        ReportViewer1.LocalReport.SetParameters(y3)
        ReportViewer1.LocalReport.SetParameters(y4)


        ReportViewer1.LocalReport.Refresh()


        'Response.Write(dt.Rows.Count & sql)
        Session("count") = dt.Rows.Count



        Return dt.Rows.Count

    End Function


    Public Function nPageNumber() As String
        Dim str As String
        str = Me.Report.Globals!PageNumber.ToString()
        Return str
    End Function

    Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
        If Session("users") Is Nothing Then
            ScriptText = "<script> wclose();</script>"
        End If
    End Sub
End Class
